package crawler;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import asa.PostBucket;

import parser.ParserAnnotation;
import parser.ParserPipeline;
import parser.SpecialHTML;

import forum.ForumFactory;
import forum.ForumPost;
import forum.ForumTag;

public class QlikViewCrawler extends Crawler {
	private Connection connection;
	private ForumFactory fac;

	private String dbname;
	private String user;
	private String password;
	private String datestart;
	private String dateend;
	private ResultSet rs;
	private boolean crawl;
	private int prevThreadId;

	public QlikViewCrawler(ForumFactory fac, ParserPipeline pipeline,
			PostBucket bucket, String dbname, String user, String password,
			String datestart, String dateend) throws Exception {
		super("QlikView", fac, pipeline, bucket);
		this.fac = fac;

		this.dbname = dbname;
		this.user = user;
		this.password = password;

		this.datestart = datestart;
		this.dateend = dateend;

		setUpConnection();
	}

	@Override
	public List<ForumPost> crawl() throws Exception {
		ArrayList<ForumPost> posts = new ArrayList<ForumPost>();

		int threadId = rs.getInt(1);
		prevThreadId = threadId;
		while (prevThreadId == threadId) {
			int postId = rs.getInt("postId");
			Date postDate = rs.getDate("PostDate");
			String date = postDate.toString();
			String subject = SpecialHTML.replaceHTML(rs.getString("subject")
					.replaceAll("^(?:(?:R[eE]|A[wW]): ?)+", ""));
			String body = rs.getString("body");
			String postAuthor = rs.getString("poster");
			if (postAuthor.equals("")) {
				postAuthor = "NoPoster";
			}
			// Hyperlink
			int applicationType = rs.getInt("applicationtype");
			String hyperlink = buildUrl(applicationType, threadId, postId,
					date, subject);

			// Get category
			String categoryName = rs.getString("section");

			// Get tags
			String tagName = rs.getString("tagName");
			if (tagName == null)
				tagName = "NoTag";
			tagName = SpecialHTML.replaceHTML(tagName);
			tagName = tagName.trim();
			if (tagName.isEmpty())
				tagName = "NoTag";
			ArrayList<ForumTag> forumTags = new ArrayList<ForumTag>();
			ForumTag tag = fac.makeTag(tagName);
			forumTags.add(tag);
			if (rs.next()) {
				crawl = true;
				threadId = rs.getInt("ThreadId");
				int prevPostId = postId;
				while (prevPostId == rs.getInt("postId")) {
					rs.getInt("tagId");
					tagName = rs.getString("tagname");
					tag = fac.makeTag(tagName);
					forumTags.add(tag);
					if (rs.next()) {
						crawl = true;
						threadId = rs.getInt("ThreadId");
					} else {
						crawl = false;
						break;
					}
				}
			} else {
				crawl = false;
			}
			ParserAnnotation pa = pp.make(body);
			posts.add(fac.makePost(subject, pa, "QlikViewForum", postAuthor,
					date, "00:00:00", hyperlink, categoryName, forumTags));

			if (!crawl) {
				break;
			}
		}
		return posts;
	}

	public void setUpConnection() throws Exception {
		Properties properties = new Properties();
		properties.put("user", user);
		properties.put("password", password);

		properties.put("namedPipe", "true");
		properties.put("TDS", "4.2");

		Class.forName("net.sourceforge.jtds.jdbc.Driver");
		connection = DriverManager.getConnection(dbname, properties);

		Statement s = connection.createStatement();

		String sql = "SELECT posts.ThreadID, posts.PostId, posts.PostDate,"
				+ "(SELECT Subject FROM communityServerLive.dbo.cs_Posts WHERE threadId=posts.threadId AND postLevel='1') AS Subject,"
				+ "CONVERT(VARCHAR(MAX), Body) AS Body,"
				+ "(SELECT UserName FROM communityServerLive.dbo.cs_Users WHERE UserId=posts.UserId) AS poster,"
				+ "categories.CategoryId as TagId,"
				+ "categories.name as TagName,"
				+ "posts.sectionId as sectionId,"
				+ "(SELECT name FROM communityServerLive.dbo.cs_Sections WHERE sectionId=posts.sectionId) as section,"
				+ "posts.applicationposttype AS applicationtype "
				+ "FROM communityServerLive.dbo.cs_Posts AS posts LEFT JOIN communityServerLive.dbo.cs_Posts_InCategories AS inCategories "
				+ "ON posts.postId=inCategories.postId LEFT JOIN communityServerLive.dbo.cs_Post_Categories AS categories "
				+ "ON inCategories.CategoryId=categories.CategoryId "
				+ "WHERE postdate BETWEEN '" + datestart + " 00:00:01' AND '"
				+ dateend + " 23:59:59'" + " ORDER BY threadId, PostDate;";
		rs = s.executeQuery(sql);
		if (rs.next()) {
			crawl = true;
		}
	}

	@Override
	public boolean canCrawl() {
		return crawl;
	}

	@Override
	public void close() throws Exception {
		connection.close();
	}

	private int pThreadId;
	private String pUrl;

	private String buildUrl(int applicationType, int threadId, int postId,
			String date, String subject) {
		String url = "http://community.qlikview.com/forums/";
		if (applicationType == 0) {
			url = "http://community.qlikview.com/forums/p/" + threadId + "/"
					+ postId + ".aspx#" + postId;
		} else if (applicationType == 1) {
			if (pThreadId == threadId) {
				url = pUrl + "#comments";
			}
		} else if (applicationType == 2) {
			url = "http://community.qlikview.com/media/p/" + postId + ".aspx";
			pUrl = url;
			pThreadId = threadId;
		} else if (applicationType == 4) {
			url = pUrl + "#comments";
		}
		return url;
	}
}
